package com.timeDeal.service;




import com.alibaba.druid.util.StringUtils;
import com.timeDeal.bean.PageList;
import com.timeDeal.bean.XuqiufabuBean;
import com.timeDeal.util.DButil;

import java.sql.*;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

/**
 * Created by 叼爸爸 on 2016/9/4.
 */
public class XuqiuService {
    /**
     * 增加一个需求发布
     * @param xfb 需求bean
     * @return  是否charity成功
     */
    public  boolean addAXuqiu(XuqiufabuBean xfb){
        Connection conn = DButil.getConnection() ;
        String sql="insert into xuqiufabu" +
                "(xuqiufabuid,xuqiuyaoqiu,yunxujingbiao,wanchengshijian,zhuangtai,jingbiaoriqi,mingcheng,jiafangid,redu,fabushijian,choulao,choulaoshangxian)" +
                " values(?,?,?,?,?,?,?,?,?,?,?,?)";
        PreparedStatement ps=null;
        ResultSet rs=null;
        XuqiufabuBean xb1 =xfb ;
        try{
            ps=conn.prepareStatement(sql);
            xb1.setXuqiufabuid(DButil.getZhuJianMax("xuqiufabuid","xuqiufabu"));
            ps.setInt(1,xb1.getXuqiufabuid());
            ps.setString(2,xb1.getMiaoshu());
            ps.setString(3,xb1.getYunxujingbiao());
            ps.setDate(4, (Date) xb1.getWanchengshijian());
            ps.setString(5,"待审核");
            ps.setDate(6, (Date) xb1.getJingbiaoriqi());
            ps.setString(7, xb1.getXuqiumingcheng());
            ps.setInt(8,xb1.getJiafangid());
            ps.setInt(9,0);
            ps.setString(10,String.valueOf(System.currentTimeMillis()));
            ps.setInt(11,xb1.getChoulao());
            ps.setInt(12,xb1.getChoulaoshangxian());
            ps.execute();
            return true;
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            try {
                if(ps!=null){
                    ps.close();
                }
                if(rs!=null){
                    rs.close();
                }
                conn.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        return  false;
    }

    /**
     * 根据id得到一个需求
     * @param id 需求id
     * @return  一个需求
     */
    public  XuqiufabuBean getAxuquById(int id ){
        Connection  mycon = DButil.getConnection() ;
        String mysql = "select  *  from xuqiufabu  where xuqiufabuid=?" ;
        PreparedStatement   ps = null ;
        ResultSet  rs =null ;
        UserService userService = new UserService() ;
        XuqiufabuBean xfb = new XuqiufabuBean() ;
        try {
            ps=mycon.prepareStatement(mysql);
            ps.setInt(1,id);
            rs = ps.executeQuery();
            if(rs.next()){
                xfb.setXuqiufabuid(rs.getInt("xuqiufabuid"));
                xfb.setMiaoshu(rs.getString("xuqiuyaoqiu"));
                xfb.setJiafangid(rs.getInt("jiafangid"));
                xfb.setYunxujingbiao(rs.getString("yunxujingbiao"));
                xfb.setWanchengshijian(rs.getDate("wanchengshijian"));
                xfb.setJingbiaoriqi(rs.getDate("jingbiaoriqi"));
                xfb.setChoulao(rs.getInt("choulao"));
                xfb.setJiafangpingjia(rs.getString("jiafangpingjia"));
                xfb.setYifangpingjia(rs.getString("yifangpingjia"));
                xfb.setXuqiumingcheng(rs.getString("mingcheng"));
                xfb.setZhuangtai(rs.getString("zhuangtai"));
                xfb.setTijiaorenXingming(userService.getDetailById(xfb.getJiafangid()));

                xfb.setChoulaoshangxian(rs.getInt("choulaoshangxian"));
                return  xfb ;
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return  null;
    }

    /**
     * 根据状态得到需求用于审核
     * @param zhuangtai 状态 ： 待审核，竞标中，交易中，交易完成
     * @param cu  当前页码
     * @return  需求list
     */
    public List<XuqiufabuBean> getAllXuqiuByZhuangtai( String zhuangtai , int cu){
        Connection cn = DButil.getConnection();
        String mysql="select  *   from  xuqiufabu   where  zhuangtai=?  limit  "+(cu*3)+",3" ;
        PreparedStatement ps=null;
        ResultSet rs=null;
        List<XuqiufabuBean>  xfb = new ArrayList<>() ;
        try{
            ps=cn.prepareStatement(mysql);
            ps.setString(1,zhuangtai);
            rs = ps.executeQuery();
            while (rs.next()){
                XuqiufabuBean xb=new XuqiufabuBean();
                xb.setXuqiumingcheng(rs.getString("mingcheng"));
                xb.setMiaoshu(rs.getString("xuqiuyaoqiu"));
                xb.setJiafangid(rs.getInt("jiafangid"));
                xb.setXuqiufabuid(rs.getInt("xuqiufabuid"));
                xfb.add(xb);
            }
            return xfb ;
        }
        catch (SQLException e) {
            e.printStackTrace();
        } finally {
            try {
                if(ps!=null){
                    ps.close();
                }
                if(rs!=null){
                    rs.close();
                }
                cn.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        return  null ;
    }

    /**
     * 根据状态得到待审核需求数量
     * @param zhuangtai  状态 ： 待审核，竞标中，交易中，交易完成
     * @return    数量
     */
    public   String  getCountByZhuangtai(String zhuangtai){
        Connection cn = DButil.getConnection();
        String mysql="select count( *)   from  xuqiufabu   where zhuangtai=?  "   ;
        PreparedStatement ps=null;
        ResultSet rs=null;
        try{
            ps=cn.prepareStatement(mysql);
            ps.setString(1,zhuangtai);
            rs = ps.executeQuery();
            if (rs.next()){
            }
            System.out.println(rs.getString(1));
            return  rs.getString(1);
        }
        catch (SQLException e) {
            e.printStackTrace();
        } finally {
            try {
                if(ps!=null){
                    ps.close();
                }
                if(rs!=null){
                    rs.close();
                }
                cn.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        return  "" ;
    }
    public PageList getXuqiuByTiaojian(int cu ,String tiaojian){
        Connection connection = DButil.getConnection();
        UserService userService = new UserService() ;
        String mysql="select count( *)   from  xuqiufabu   where zhuangtai=?  "   ;
        String mysqlyunxu="select count( *)   from  xuqiufabu   where zhuangtai=?  and yunxujingbiao =? "   ;
        int pagesize = PageList.pagesize ;
        String mysqlleft="select *   from  xuqiufabu   where zhuangtai=?   "  ;
        String  mysqldown   =  "  DESC   limit   "+ (cu*pagesize-pagesize)+", "+pagesize   ;
        String  mysqljingbiao  =  "select *   from  xuqiufabu   where zhuangtai=?  and  yunxujingbiao=? " ;
        String order = "   ORDER BY   " ;
        PageList  pageList =  new PageList() ;
        List<Object> list =new ArrayList<>() ;
        PreparedStatement ps=null;
        ResultSet rs=null;
        if(StringUtils.equals(tiaojian,"热度")){
            mysqlleft = mysqlleft +order+ "redu" + mysqldown ;
        }
        if(StringUtils.equals(tiaojian,"发布时间")){
            mysqlleft = mysqlleft  +order+ "fabushijian" + mysqldown ;
        }
        if(StringUtils.equals(tiaojian,"酬劳金额")){
            mysqlleft = mysqlleft  +order+ "choulao" + mysqldown ;
        }
        try{
            if(StringUtils.equals(tiaojian,"允许竞标")){
                ps=connection.prepareStatement(mysqlyunxu);
                ps.setString(2,"是");
            }
            else if(StringUtils.equals(tiaojian,"不允许竞标")){
                ps=connection.prepareStatement(mysqlyunxu);
                ps.setString(2,"否");
            }
            else{ ps=connection.prepareStatement(mysql);}
            ps.setString(1,"审核通过");
            rs = ps.executeQuery();
            if (rs.next()){
            }
            pageList.setTotalpage(rs.getInt(1)%pagesize==0?rs.getInt(1)/pagesize:rs.getInt(1)/pagesize+1) ;
            pageList.setCurrentpage(cu);
            ps.clearParameters();
            if(StringUtils.equals(tiaojian,"允许竞标")){
                mysqlleft = mysqljingbiao  +order+ "fabushijian" + mysqldown ;
                ps=connection.prepareStatement(mysqlleft);
                ps.setString(2,"是");
            }
           else if(StringUtils.equals(tiaojian,"不允许竞标")){
                mysqlleft = mysqljingbiao   + order+"fabushijian"+  mysqldown  ;
                ps=connection.prepareStatement(mysqlleft);
                ps.setString(2,"否");
            }
           else {  ps=connection.prepareStatement(mysqlleft);}
            System.out.println(mysqlleft);
            ps.setString(1,"审核通过");
            rs=ps.executeQuery() ;
            while (rs.next()){
                XuqiufabuBean xuqiufabuBean = new XuqiufabuBean() ;
                if(StringUtils.equals(tiaojian,"允许竞标")&&rs.getString("yunxujingbiao")=="否"){
                    continue;
                }
                if(StringUtils.equals(tiaojian,"不允许竞标")&&rs.getString("yunxujingbiao")=="是"){
                    continue;
                }
                xuqiufabuBean.setXuqiumingcheng(rs.getString("mingcheng"));
                xuqiufabuBean.setXuqiufabuid(rs.getInt("xuqiufabuid"));
                xuqiufabuBean.setRedu(rs.getInt("redu"));
                xuqiufabuBean.setJiafangid(rs.getInt("jiafangid"));
                xuqiufabuBean.setTijiaorenXingming(userService.getDetailById(rs.getInt("jiafangid")));
                xuqiufabuBean.setFabushijian(rs.getDate("fabushijian"));
                xuqiufabuBean.setChoulao(rs.getInt("choulao"));
                xuqiufabuBean.setYunxujingbiao(rs.getString("yunxujingbiao"));
                xuqiufabuBean.setChoulaoshangxian(rs.getInt("choulaoshangxian"));
                list.add(xuqiufabuBean);
            }
            pageList.setList(list);
            return  pageList ;
        }
        catch (SQLException e) {
            e.printStackTrace();
        } finally {
            try {
                if(ps!=null){
                    ps.close();
                }
                if(rs!=null){
                    rs.close();
                }
                connection.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        return  null ;
    }

    /**
     * 根据需求发布时间得到 需求
     * @param cu  当前页码
     * @return  PageList
     */
    public PageList getXuqiuByFabushijian(int cu){
        Connection connection = DButil.getConnection();
        UserService userService = new UserService() ;
        String mysql="select count( *)   from  xuqiufabu   where zhuangtai=?  "   ;
        int pagesize = PageList.pagesize ;
        String mysql2="select *   from  xuqiufabu   where zhuangtai=?  ORDER by  fabushijian DESC   limit   "+ (cu*pagesize-pagesize)+", "+pagesize   ;
        PageList  pageList =  new PageList() ;
        List<Object> list =new ArrayList<>() ;
        PreparedStatement ps=null;
        ResultSet rs=null;
        try{
            ps=connection.prepareStatement(mysql);
            ps.setString(1,"审核通过");
            rs = ps.executeQuery();
            if (rs.next()){
            }
            pageList.setTotalpage(rs.getInt(1)/pagesize+1) ;
            pageList.setCurrentpage(cu);
            ps.clearParameters();
            ps=connection.prepareStatement(mysql2);
            ps.setString(1,"审核通过");
            rs=ps.executeQuery() ;
            while (rs.next()){
                XuqiufabuBean xuqiufabuBean = new XuqiufabuBean() ;
                xuqiufabuBean.setXuqiumingcheng(rs.getString("mingcheng"));
                xuqiufabuBean.setXuqiufabuid(rs.getInt("xuqiufabuid"));
                xuqiufabuBean.setRedu(rs.getInt("redu"));
                xuqiufabuBean.setJiafangid(rs.getInt("jiafangid"));
                xuqiufabuBean.setTijiaorenXingming(userService.getDetailById(rs.getInt("jiafangid")));
                xuqiufabuBean.setFabushijian(rs.getDate("fabushijian"));
                System.out.println(rs.getString("mingcheng"));
                list.add(xuqiufabuBean);
            }
            pageList.setList(list);
            return  pageList ;
        }
        catch (SQLException e) {
            e.printStackTrace();
        } finally {
            try {
                if(ps!=null){
                    ps.close();
                }
                if(rs!=null){
                    rs.close();
                }
                connection.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        return  null ;
    }
    public boolean tijiaoPingjia(int id, int flag,String pingjia) {
        Connection conn = DButil.getConnection();
        String mysql = "update xuqiufabu set jiafangpingjia=? where xuqiufabuid=?";
        String mysq2 = "update xuqiufabu set yifangpingjia=? where xuqiufabuid=?";
        PreparedStatement ps = null;
        boolean re =false ;
        try {
            if(flag>0) {
                ps = conn.prepareStatement(mysql);
                ps.setString(1, pingjia);
                ps.setInt(2, id);
                re = ps.execute();
            }
            else{
                ps = conn.prepareStatement(mysq2);
                ps.setString(1,pingjia);
                ps.setInt(2, id);
                re = ps.execute();
            }
            return re;
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            try {
                if (ps != null) {
                    ps.close();
                }
                conn.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        return false;
    }
    public boolean zengJiaRedu(int id) {
        Connection conn = DButil.getConnection();
        String mysql = "update xuqiufabu set redu=ifnull(redu,0)+1 where xuqiufabuid=?";
        PreparedStatement ps = null;
        try {
                ps = conn.prepareStatement(mysql);
                ps.setInt(1, id);
                ps.execute();
            return true;
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            try {
                if (ps != null) {
                    ps.close();
                }
                conn.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        return false;
    }
    /**
     * 根据状态得到待审核需求数量
     * @param id   id
     * @return    数量
     */
    public   String  getCountByJiafangid(int id){
        Connection cn = DButil.getConnection();
        String mysql="select count( *)   from  xuqiufabu   where zhuangtai=? and jiafangid=?  "   ;
        PreparedStatement ps=null;
        ResultSet rs=null;
        try{
            ps=cn.prepareStatement(mysql);
            ps.setString(1,"交易完成");
            ps.setInt(2,id);
            rs = ps.executeQuery();
            if (rs.next()){
            }
            return  rs.getString(1);
        }
        catch (SQLException e) {
            e.printStackTrace();
        } finally {
            try {
                if(ps!=null){
                    ps.close();
                }
                if(rs!=null){
                    rs.close();
                }
                cn.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        return  "" ;
    }
    /**
     * 根据状态得到待审核需求数量
     * @param id   id
     * @return    数量
     */
    public   String  getCountByYifangid(int id){
        Connection cn = DButil.getConnection();
        String mysql="select count( *)   from  xuqiufabu   where zhuangtai=? and yifangid=?  "   ;
        PreparedStatement ps=null;
        ResultSet rs=null;
        try{
            ps=cn.prepareStatement(mysql);
            ps.setString(1,"交易完成");
            ps.setInt(2,id);
            rs = ps.executeQuery();
            if (rs.next()){
            }
            return  rs.getString(1);
        }
        catch (SQLException e) {
            e.printStackTrace();
        } finally {
            try {
                if(ps!=null){
                    ps.close();
                }
                if(rs!=null){
                    rs.close();
                }
                cn.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        return  "" ;
    }
    public boolean tiqiangg(int id) {
        Connection conn = DButil.getConnection();
        String mysql = "update xuqiufabu set zhuangtai=? where xuqiufabuid=?";
        PreparedStatement ps = null;
        try {
            ps = conn.prepareStatement(mysql);
            ps.setString(1,"交易完成");
            ps.setInt(2, id);
            ps.execute();
            return true;
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            try {
                if (ps != null) {
                    ps.close();
                }
                conn.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        return false;
    }
}
